package com.ruoyi.project.system.mapper;

import java.util.List;

import com.ruoyi.project.system.domain.CourseStudy;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * 课程学习Mapper接口
 * 
 * @author ruoyi
 * @date 2020-10-07
 */
@Mapper
public interface CourseStudyMapper 
{
    /**
     * 查询课程学习
     * 
     * @param id 课程学习ID
     * @return 课程学习
     */
    public CourseStudy selectCourseStudyById(Integer id);

    /**
     * 查询课程学习列表
     * 
     * @param courseStudy 课程学习
     * @return 课程学习集合
     */
    public List<CourseStudy> selectCourseStudyList(CourseStudy courseStudy);

    /**
     * 新增课程学习
     * 
     * @param courseStudy 课程学习
     * @return 结果
     */
    public int insertCourseStudy(CourseStudy courseStudy);

    /**
     * 修改课程学习
     * 
     * @param courseStudy 课程学习
     * @return 结果
     */
    public int updateCourseStudy(CourseStudy courseStudy);

    /**
     * 删除课程学习
     * 
     * @param id 课程学习ID
     * @return 结果
     */
    public int deleteCourseStudyById(Integer id);

    /**
     * 批量删除课程学习
     * 
     * @param ids 需要删除的数据ID
     * @return 结果
     */
    public int deleteCourseStudyByIds(Integer[] ids);


    @Select("select count(*) from course_study where create_dt>=current_date")
    int selectSumCount();

    @Select("select distinct A.name from (select substring(to_char(create_dt,'yyyy-MM-dd hh24:MI:ss') FROM 6 FOR 6) as name  from course_study as n where n.create_dt>=to_timestamp(substring(to_char(now(),'yyyy-MM-dd hh24:MI:ss') FROM 1 FOR 10),'yyyy-MM-dd')- interval '30 day' order by create_dt asc) as A ORDER BY name asc")
    List<String> selectCourseName();

    @Select("select count(1) from (select substring(to_char(create_dt,'yyyy-MM-dd hh24:MI:ss') FROM 6 FOR 6) as name  from course_study as n where n.create_dt>=to_timestamp(substring(to_char(now(),'yyyy-MM-dd hh24:MI:ss') FROM 1 FOR 10),'yyyy-MM-dd')- interval '30 day' order by create_dt asc) as A GROUP BY name ORDER BY name asc")
    List<Integer> selectCourseCount();

    @Select("select count(*) from course_study where create_dt>=current_date and college_id=#{college_id}")
    int selectSumCountCollegeId(@Param("college_id") Long college_id);

    @Select("select distinct A.name from (select substring(to_char(create_dt,'yyyy-MM-dd hh24:MI:ss') FROM 6 FOR 6) as name  from course_study as n where n.college_id=#{college_id} and n.create_dt>=to_timestamp(substring(to_char(now(),'yyyy-MM-dd hh24:MI:ss') FROM 1 FOR 10),'yyyy-MM-dd')- interval '30 day' order by create_dt asc) as A ORDER BY name asc")
    List<String> selectCourseNameCollegeId(@Param("college_id") Long college_id);

    @Select("select count(1) from (select substring(to_char(create_dt,'yyyy-MM-dd hh24:MI:ss') FROM 6 FOR 6) as name  from course_study as n where n.college_id=#{college_id} and n.create_dt>=to_timestamp(substring(to_char(now(),'yyyy-MM-dd hh24:MI:ss') FROM 1 FOR 10),'yyyy-MM-dd')- interval '30 day' order by create_dt asc) as A GROUP BY name ORDER BY name asc")
    List<Integer> selectCourseCountCollegeId(@Param("college_id") Long college_id);
}
